package com.ssyt.ec.stu.service;

import java.util.ArrayList;
import java.util.List;

import com.ssyt.ec.common.PageModel;
import com.ssyt.ec.common.util.DBUtil;
import com.ssyt.ec.stu.entity.ExamInfoEnetity;
import com.ssyt.ec.stu.entity.PersonalEntity;
import com.ssyt.ec.stu.entity.StationEntity;
import com.ssyt.ec.stu.entity.ViewExamEntity;

public class StuService {

	/**
	 * 考试安排
	 * 
	 * @param userId
	 * @param classId
	 * @param rows
	 * @param page
	 * @return
	 */
	public PageModel<ViewExamEntity> listStu(int userId, int classId, int page,
			int rows) {
		List<Object> paramList = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder("SELECT e.id,");
		sql.append("e.exam_time_start,e.exam_time_stop,e.exam_classroom, ");
		sql.append("q.name AS pagename,s.name AS username,e.operate_time ");
		sql.append("FROM exam_plan e,qes_exam_paper q,sys_user s ");
		sql.append("WHERE e.exam_paper_id=q.id AND e.operate_user_id=s.id AND ");
		sql.append("(e.to_user_ids LIKE ? ");
		paramList.add("%," + userId + ",%");
		sql.append("OR e.to_class_id=?) ");
		paramList.add(classId);
		sql.append(" AND not exists(SELECT r.id FROM exam_records r WHERE r.user_id=? AND r.exam_plan_id=e.id) ");
		paramList.add(userId);
		sql.append(" AND e.state=1 AND NOW()<e.exam_time_stop ORDER BY e.id DESC LIMIT ?,?");
		paramList.add((page - 1) * rows);
		paramList.add(rows);
		return DBUtil.executeQueryByPage(ViewExamEntity.class, sql.toString(),
				paramList);
	}

	/**
	 * 考试记录
	 * 
	 * @param paperName
	 * @param isPass
	 * @param page
	 * @param rows
	 * @param userId
	 * @return
	 */
	public PageModel<ExamInfoEnetity> listExamInfo(String paperName,
			int isPass, int page, int rows, int userId) {
		List<Object> paramList = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder(
				"SELECT q.name AS pagename,er.start_time,er.submit_time,q.total_point,er.get_point,er.is_pass ");
		sql.append("FROM exam_records er,exam_plan ep,qes_exam_paper q ");
		sql.append("WHERE ep.exam_paper_id=q.id  AND er.user_id=? ");
		paramList.add(userId);
		if (isPass != 0) {
			sql.append("AND er.is_pass=? ");
			paramList.add(isPass);
		}
		if (paperName != null) {
			sql.append("AND q.name LIKE ? ");
			paramList.add("%" + paperName + "%");
		}
		sql.append("ORDER BY er.id DESC LIMIT ?,?");
		paramList.add((page - 1) * rows);
		paramList.add(rows);
		return DBUtil.executeQueryByPage(ExamInfoEnetity.class, sql.toString(),
				paramList);
	}

	/**
	 * 站内信息
	 * 
	 * @param userId
	 * @param classId
	 * @param rows
	 * @param page
	 * @return
	 */
	public PageModel<StationEntity> listStation(int userId, int classId,
			int page, int rows) {
		List<Object> paramList = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder("SELECT a.id,a.title,a.content, ");
		sql.append("su.name AS sendername,sr.name AS receivername,a.send_time,a.expire_time ");
		sql.append("FROM assist_message a,sys_user su,sys_role sr ");
		sql.append("WHERE a.sender_id=su.id AND su.role_id=sr.id ");
		sql.append("AND (a.receiver_user_ids LIKE ? ");
		paramList.add("%," + userId + ",%");
		sql.append("OR a.receiver_class_ids=?) ");
		paramList.add(classId);
		sql.append("AND NOW()<a.expire_time ORDER BY a.id DESC LIMIT ?,?");
		paramList.add((page - 1) * rows);
		paramList.add(rows);
		return DBUtil.executeQueryByPage(StationEntity.class, sql.toString(),
				paramList);
	}

	/**
	 * 站内信息详细查看
	 * 
	 * @param id
	 * @return
	 */
	public StationEntity listStationMinute(int id) {
		StationEntity stationEntity = new StationEntity();
		StringBuilder sql = new StringBuilder(
				"SELECT a.title,a.content,su.name AS sendername, ");
		sql.append("sr.name AS receivername,a.send_time,a.expire_time ");
		sql.append("FROM assist_message a,sys_user su,sys_role sr ");
		sql.append("WHERE a.sender_id=su.id AND su.role_id=sr.id AND a.id=?");
		stationEntity = DBUtil.getUniqueResult(StationEntity.class,
				sql.toString(), id);
		return stationEntity;
	}

	/**
	 * 个人信息查询
	 * 
	 * @param page
	 * @param rows
	 * @param userId
	 * @return
	 */
	public PageModel<PersonalEntity> listPersonal(int page, int rows, int userId) {
		String sql = "SELECT su.id,su.code,su.name AS username,su.sex,"
				+ "su.phone,su.email,su.create_time,sr.name AS rolename,"
				+ "sc.name AS classname FROM sys_user su,sys_role sr,sys_class sc "
				+ "WHERE su.role_id=sr.id AND su.class_id=sc.id AND su.id=? LIMIT ?,?;";
		return DBUtil.executeQueryByPage(PersonalEntity.class, sql, userId,
				(page - 1) * rows, rows);
	}

	/**
	 * 主页面学员代考查看
	 * 
	 * @param userId
	 * @param classId
	 * @return
	 */
	public ViewExamEntity listStuViewExam(int userId, int classId) {
		List<Object> paramList = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder("SELECT e.id,");
		sql.append("e.exam_time_start,e.exam_time_stop,e.exam_classroom, ");
		sql.append("q.name AS pagename,s.name AS username,e.operate_time ");
		sql.append("FROM exam_plan e,qes_exam_paper q,sys_user s ");
		sql.append("WHERE e.exam_paper_id=q.id AND e.operate_user_id=s.id ");
		sql.append("AND (e.to_user_ids LIKE ? ");
		paramList.add("%," + userId + ",%");
		sql.append("OR e.to_class_id=?) ");
		paramList.add(classId);
		sql.append(" AND not exists(SELECT r.id FROM exam_records r WHERE r.user_id=? AND r.exam_plan_id=e.id) ");
		paramList.add(userId);
		sql.append(" AND e.state=1 AND NOW()<e.exam_time_stop ORDER BY e.id DESC");
		return DBUtil.getUniqueResult(ViewExamEntity.class, sql.toString(),
				paramList);
	}

	public StationEntity listStuStationInfo(int userId, int classId) {
		List<Object> paramList = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder("SELECT a.id,a.title,a.content, ");
		sql.append("su.name AS sendername,sr.name AS receivername,a.send_time,a.expire_time ");
		sql.append("FROM assist_message a,sys_user su,sys_role sr ");
		sql.append("WHERE a.sender_id=su.id AND su.role_id=sr.id ");
		sql.append("AND (a.receiver_user_ids LIKE ? ");
		paramList.add("%," + userId + ",%");
		sql.append("OR a.receiver_class_ids=?) ");
		paramList.add(classId);
		sql.append("AND NOW()<a.expire_time ORDER BY a.id DESC");
		return DBUtil.getUniqueResult(StationEntity.class, sql.toString(),
				paramList);
	}
}
